<?php
require_once("dbconnection.php");
class SNDataManager
{
public	function SNDataManager()
	{
       //$this->$db = new Database($host,$db_username,$db_password,$db_name);
	}
    //$sql = "SELECT * FROM `".$config['table']."` ORDER BY $orderby $sort LIMIT $startrow,$limit";
    
	public	function getAllUserPaging($Admin_flag,$orderby, $sort,$startrow,$limit)
        {
         	global $connection;
			    $command= "SELECT ACC.CODE,ACC.FISRT_NAME,ACC.LAST_NAME,ACC.LOGIN_NAME,LV.LEV_NAME,ACC.ID FROM ACCOUNT ACC INNER JOIN LEVEL LV ON ACC.LEVEL_ID=LV.ID WHERE LV.ACTIVE =1 AND ACC.ACTIVE =1 ";
					if(!$Admin_flag)
					{
						$command =  $command." AND LV.ADMIN_FLAG <> 1";
					}
                    $command = $command." ORDER BY ".$orderby." ".$sort." LIMIT ".$startrow.",".$limit;
                 //echo $command; 
			    $findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
		        return $findResult;
        }
      
      public	function getAllUserInfo($Admin_flag)
        {
         	global $connection;

			    $command= "SELECT ACC.*,LV.LEV_NAME FROM ACCOUNT ACC INNER JOIN LEVEL LV ON ACC.LEVEL_ID=LV.ID WHERE LV.ACTIVE =1 AND ACC.ACTIVE =1 ";
					if(!$Admin_flag)
					{
						$command =  $command." AND LV.ADMIN_FLAG <> 1";
					}

			    $findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
		        return $findResult;
        }

      public function CheckDupicateUserInfo($user_id,$login_name,$user_code)
      {
      	   global $connection;
              $result= false;
			    $command= "SELECT * FROM ACCOUNT WHERE ( LOGIN_NAME='".$login_name."' OR CODE = '".$user_code."') AND ID <> ".$user_id ."  LIMIT 1 ";
			    // echo $command;
                $findResult = mysql_query($command, $connection) or die(mysql_error());
			    while($row = mysql_fetch_array($findResult,MYSQL_ASSOC));
			    {
                      $result =$row["ID"]>0;
			    }
                return $result;
      }
      public function ChangePasswords($user_id,$old_passwords,$new_passwords)
      {
            global $connection;
      		$hash = md5($old_passwords);
      		$hash_new = md5($new_passwords);

      		$command_upd = "UPDATE ACCOUNT
							SET LOG_PASS='".$hash_new."',
                            UP_DATE = NOW()
                            WHERE  ID = ".$user_id." AND LOG_PASS='".$hash."'";
         //echo $command_upd;
          mysql_query($command_upd, $connection) or die(mysql_error());
          return mysql_affected_rows()>0;
      }

      public function FindUserInfo($user_id)
      {
            global $connection;

			    $command= "SELECT * FROM ACCOUNT WHERE ID=".$user_id;

			    $findResult = mysql_query($command, $connection) or die(mysql_error());
                return $findResult;
			    //$resultRow = mysql_fetch_array($findResult) or die(mysql_error());
                //return new UserInfo($findResult[0]['CODE'],$findResult[0]['ID'],$findResult[0]['FIRST_NAME'],$findResult[0]['LAST_NAME'],$findResult[0]['LOGIN_NAME'],$findResult[0]['LOG_PASS']);
      }

      public function FindSerialNumber($sn_Number)
      {
            global $connection;

            $command= "SELECT * FROM SN_NUMBER WHERE SERIAL_NUMBER ='".$sn_Number."' AND ACTIVE=1";
            $findResult = mysql_query($command, $connection) or die(mysql_error());
           return $findResult;
      }

     public function GetAllLevel($Admin_flag)
        {
        	global $connection;
        	   $command= "SELECT * FROM  LEVEL LV WHERE LV.ACTIVE =1 ";
        	   if(!$Admin_flag)
				{
					$command =  $command." AND LV.ADMIN_FLAG <> 1";
				}
			    $findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
		        return $findResult;
        }

        public function GetAllSNNumber()
        {
            global $connection;
        	$command= "SELECT SN.ID,SN.SERIAL_NUMBER,SN.GENERATE_DATE,SN.IMPORT_DATE,
                         concat(concat(AC.FISRT_NAME,' '),AC.LAST_NAME) AS IMPORT_BY FROM  
                        SN_NUMBER SN LEFT JOIN ACCOUNT AC ON SN.IMPORT_BY=AC.ID WHERE SN.ACTIVE =1 ";

			$findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
		    return $findResult;
        }

       public  function RemoveUser($ID)
        {
            global $connection;
            $command_upd = "UPDATE ACCOUNT
							SET ACTIVE=0,
                            UP_DATE = NOW()
                            WHERE  ID = ".$ID;
            //echo $command_upd;
            $findResult = mysql_query($command_upd, $connection) or die(mysql_error());
             return mysql_affected_rows()>0;
        }
        
    public function CheckDupicateSNNumber($sn_Number)
    {
         global $connection;

        $command= "SELECT * FROM SN_NUMBER WHERE SERIAL_NUMBER ='".$sn_Number."' LIMIT 1";
        $findResult = mysql_query($command, $connection) or die(mysql_error());
        return $findResult;
    }
    
    public  function RemoveSNNumber($ID)
        {
            global $connection;
            $command_upd = "DELETE FROM SN_NUMBER WHERE  ID = ".$ID;
            //echo $command_upd;
            $findResult = mysql_query($command_upd, $connection) or die(mysql_error());
             return mysql_affected_rows()>0;
        }
    
    public    function ImportSNNumber($SN_NO,$GEN_DATE,$user_id)
        {
            global $connection;
            $command ="INSERT INTO SN_NUMBER (SERIAL_NUMBER,GENERATE_DATE,IMPORT_DATE,IMPORT_BY)
                        VALUES ('".$SN_NO."','".date_format($GEN_DATE,"Y/m/d")."',NOW(),".$user_id.")";
           $findResult = mysql_query($command, $connection) or die(mysql_error());
            return mysql_affected_rows()>0;
        }

    public    function SaveUserInfo($ID,$login_name,$login_pass,$CODE,$LEVEL_ID,$FISRT_NAME,$LAST_NAME)
		{
		      global $connection;
			//$t_hasher = new PasswordHash(8, FALSE);
			//$hash = password_hash("rasmuslerdorf", PASSWORD_BCRYPT, $options);//$t_hasher->HashPassword($login_pass);
			$hash = md5($login_pass);
			$command_add = "INSERT INTO ACCOUNT(CODE,LEVEL_ID,FISRT_NAME,LAST_NAME,LOGIN_NAME,LOG_PASS,CREATE_DATE,UP_DATE)
						VALUES ('".$CODE."',".$LEVEL_ID.",'".$FISRT_NAME."','".$LAST_NAME."','".$login_name."','".$hash."', NOW() , NOW())";

			$command_upd = "UPDATE ACCOUNT
							SET CODE='".$CODE."',
							LEVEL_ID=".$LEVEL_ID.",
							FISRT_NAME ='".$FISRT_NAME."',
							LAST_NAME ='".$LAST_NAME."',
							LOGIN_NAME='".$login_name."',
                            UP_DATE = NOW()
                            WHERE  ID = ".$ID;

			if($ID==null || $ID <= 0)
			{
				$findResult = mysql_query($command_add, $connection) or die(mysql_error());

			}else
			{
			     //echo $command_upd;
				$findResult = mysql_query($command_upd, $connection) or die(mysql_error());
			}
             return mysql_affected_rows()>0;
		}

    public	 function UserAuthenticate($login_name,$login_pass)
     {   
        global $connection;
			$hash = md5($login_pass);
			$command = "SELECT ACC.*,LV.LEV_NAME,LV.ADMIN_FLAG FROM ACCOUNT ACC
						INNER JOIN LEVEL LV ON ACC.LEVEL_ID=LV.ID WHERE LV.ACTIVE =1
						AND ACC.ACTIVE =1  AND  LOGIN_NAME ='".$login_name."' AND LOG_PASS ='".$hash."'";
           $findResult = mysql_query($command, $connection) or die(mysql_error());
           return $findResult;
		}

    public function SaveComment($sn_number,$cont_id_card,$cont_name,$cont_email,$cont_phone,$comment)
    {
        global $connection;   
        $command = "INSERT INTO SN_COMMENT (SERIAL_NUMBER,ID_CARD_NO,NAME,EMAIL,PHONE,COMMENT,CREATE_DATE)
                    VALUES('".$sn_number."','".$cont_id_card."','".$cont_name."','".$cont_email."','".$cont_phone."','".$comment."',NOW())";
        $findResult = mysql_query($command, $connection) or die(mysql_error());
        echo mysql_error();
        return mysql_affected_rows()>0;
    }
    public function CheckDupicateComment($sn_number)
    {
        global $connection;   
        $command = "SELECT SERIAL_NUMBER,ID_CARD_NO,NAME,EMAIL,PHONE,COMMENT,CREATE_DATE FROM  SN_COMMENT
                    WHERE SERIAL_NUMBER='".$sn_number."'";
        $findResult = mysql_query($command, $connection) or die(mysql_error());
      
        return $findResult;
    }
     public  function redirect($url)
     {
        if (headers_sent()){
          die('<script type="text/javascript">window.location.href="' . $url . '";</script>');
        }else{
          header('Location: ' . $url);
          die();
        }    
    }
    
    public function FindComment($id)
    {
        $command ="SELECT * FROM SN_COMMENT WHERE ID=".$id;
        global $connection;  
         $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    public function GetTopUser($top)
    {
          global $connection;  
        $command = "SELECT * FROM ACCOUNT WHERE ACTIVE =1 LIMIT ".$top;
         $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    public function GetUserRowCount()
    {
        global $connection;  
        $command = "SELECT COUNT(*) AS total FROM ACCOUNT WHERE ACTIVE =1";
        	
        $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    public function GetSNRowCount($where_date)
    {
         global $connection;  
        $command = "SELECT COUNT(*) AS total FROM SN_NUMBER WHERE ACTIVE =1";
        if($where_date)
                {
				    $command = $command." AND GENERATE_DATE='".$where_date."'";
				}
        $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    public function GetTopSN($top){
        
        global $connection;  
        $command = "SELECT  SN.ID
                            ,SN.SERIAL_NUMBER
                            ,SN.GENERATE_DATE
                            ,SN.IMPORT_DATE
                            ,AC.CODE AS IMPORT_BY
                            FROM SN_NUMBER SN 
                            LEFT JOIN ACCOUNT AC ON SN.IMPORT_BY=AC.ID 
                            WHERE SN.ACTIVE =1 LIMIT ".$top ." ;";
          //  echo     $command;            
         $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    
    public	function getAllSNPaging($orderby, $sort,$startrow,$limit,$where_date)
        {
         	global $connection;
			    $command= "SELECT SN.ID
                            ,SN.SERIAL_NUMBER
                            ,SN.GENERATE_DATE
                            ,SN.IMPORT_DATE
                            ,AC.CODE AS IMPORT_BY
                            FROM SN_NUMBER SN 
                            LEFT JOIN ACCOUNT AC ON SN.IMPORT_BY=AC.ID 
                            WHERE SN.ACTIVE =1 ";
				if($where_date)
                {
				    $command = $command." AND GENERATE_DATE='".$where_date."'";
				}
                $command = $command." ORDER BY ".$orderby." ".$sort." LIMIT ".$startrow.",".$limit." ;";
              //  echo     $command;
                //echo $command;
			    $findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
             
		        return $findResult;
        }
        
    public function GetCommentRowCount()
    {
         global $connection;  
        $command = "SELECT COUNT(*) AS total FROM SN_COMMENT";
        $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    public function GetTopCommnet($top){
        
        global $connection;  
        $command = "SELECT  SN.ID
                            ,SN.CREATE_DATE
                            ,SN.SERIAL_NUMBER
                            ,SN.NAME
                            ,SN.ID_CARD_NO
                            ,SN.EMAIL
                            ,SN.PHONE
                            FROM SN_COMMENT SN LIMIT ".$top ." ;";
            //echo     $command;            
         $findResult = mysql_query($command, $connection) or die(mysql_error());
         return $findResult;
    }
    
    public	function getAllCommentPaging($orderby, $sort,$startrow,$limit)
        {
         	global $connection;
			    $command= "SELECT SN.ID
                            ,SN.CREATE_DATE
                            ,SN.SERIAL_NUMBER
                            ,SN.NAME
                            ,SN.ID_CARD_NO
                            ,SN.EMAIL
                            ,SN.PHONE
                            FROM SN_COMMENT SN ";
				
                $command = $command." ORDER BY ".$orderby." ".$sort." LIMIT ".$startrow.",".$limit." ;";
                //echo     $command;
                
			    $findResult = mysql_query($command, $connection) or die(mysql_error());
			   // $resultRow = mysql_fetch_array($findResult) or die(mysql_error());
		        return $findResult;
        }
        
        
    function columnSortArrows($field,$text,$currentfield=null,$currentsort=null)
    {	
	//defaults all field links to SORT ASC
	//if field link is current ORDERBY then make arrow and opposite current SORT
	
        	$sortquery = "sort=ASC";
        	$orderquery = "orderby=".$field;
        	
        	if($currentsort == "ASC"){
        		$sortquery = "sort=DESC";
        		$sortarrow = '<img src="images/arrow_up.png" />';
        	}
        	
        	if($currentsort == "DESC"){
        		$sortquery = "sort=ASC";
        		$sortarrow = '<img src="images/arrow_down.png" />';
        	}
        	
        	if($currentfield == $field){
        		$orderquery = "orderby=".$field;
        	}else{	
        		$sortarrow = null;
        	}
        	
        	return '<a href="?'.$orderquery.'&'.$sortquery.'">'.$text.'</a> '. $sortarrow;	
	
    }
    function IsNullOrEmpty($input)
    {
        return (!isset($input) || trim($input)==='');
    }
    
    function getUrlCurrently($filter = array()) 
    {
    $pageURL = isset($_SERVER["HTTPS"]) && $_SERVER["HTTPS"] == "on" ? "https://" : "http://";

    $pageURL .= $_SERVER["SERVER_NAME"];

    if ($_SERVER["SERVER_PORT"] != "80") {
        $pageURL .= ":".$_SERVER["SERVER_PORT"];
    }

    $pageURL .= $_SERVER["REQUEST_URI"];


    if (strlen($_SERVER["QUERY_STRING"]) > 0) {
        $pageURL = rtrim(substr($pageURL, 0, -strlen($_SERVER["QUERY_STRING"])), '?');
    }

    $query = $_GET;
    foreach ($filter as $key) {
        unset($query[$key]);
    }

    if (sizeof($query) > 0) {
        $pageURL .= '?' . http_build_query($query);
    }

    return $pageURL;
}

}
?>